#
#  osm2sql.py - OpenStreetMap to 'SQL insert query' converter
#
#
#  Required:
#    * Beautiful Soup (http://www.crummy.com/software/BeautifulSoup/)
#
#
#  Licenced under GPL v2
#

import sys

from BeautifulSoup import BeautifulStoneSoup 

def quotedstr(st):
  return "'" + st.replace("'", "''") + "'"

#INSERT INTO node_tags (id, k, v) VALUES ('6920821', 'highway', 'mini_roundabout');
#INSERT INTO way_tags (id, k, v) VALUES ('3870673', 'highway', 'secondary');
#INSERT INTO relation_tags (id, k, v) VALUES ('5008', 'created_by', 'JOSM');
def print_tag(table, id, k, v):
  print ("insert into " + table + "(id, k, v) values(" + id + ", " + quotedstr(k) + ", " + quotedstr(v) + ");").encode('utf8')

#INSERT INTO ways_nodes (wayid, nodeid, sequence) VALUES ('3870673', '8220538', '1');
def print_way_nodes(wayid, nodeid, sequence):
  print "insert into ways_nodes(wayid, nodeid, sequence) values(" + str(wayid) + ", " + str(nodeid) + ", " + str(sequence) + ");"

#INSERT INTO nodes (id, lat, lon, visible, _user, timestamp) VALUES ('488549', '57.8268758', '27.9074961', NULL, 'usm78-gis', '2008-06-16 00:03:29');
#INSERT INTO ways (id, visible, _user, timestamp) VALUES ('3870673', NULL, 'k__', '2011-12-27 19:24:37');
#INSERT INTO relations (id, visible, _user, timestamp) VALUES ('5008', NULL, 'mea_john', '2008-02-02 12:06:24');
def print_object(table, id, lat, lon, visible, user, timestamp):
  if table == "nodes":
    print "insert into nodes(id, lat, lon, visible, _user, timestamp) values(" + id + ", " + lat + ", " + lon + ", " +  visible  + ", " + quotedstr(user) + ", " + quotedstr(timestamp) + ");"
  else:
    print "insert into " + table + "(id, visible, _user, timestamp) values(" + id + ", " + visible + ", " + quotedstr(user) + ", " + quotedstr(timestamp) + ");"


#INSERT INTO member_node (nodeid, relid, role) VALUES ('27503927', '60189', 'admin_centre');
def print_member(table, rel_id, member_id, role):
  if table == "nodes":
    q = "insert into member_node(nodeid, relid, role)"
  elif table == "way":
    q = "insert into member_way(wayid, relid, role)"
  else:
    q = "insert into member_relation(relid2, relid, role)"
  print (q + "values(" + member_id + ", " + rel_id + ", " + quotedstr(role) + ");").encode('utf8')
	
_VERSION = '0.01';

ifilename = sys.argv[1]
#ofilename = "undef.log"

ifile = open(ifilename, "r")
#ofile = open(ofilename, "w")

soup = BeautifulStoneSoup(''.join(ifile.read()))
seq = 1

for xmltag in soup.findAll(True):
  # Print objects
  if xmltag.name == 'node':
	print_object("nodes", xmltag['id'], xmltag['lat'], xmltag['lon'], xmltag['visible'], xmltag['user'], xmltag['timestamp'])
  elif xmltag.name == 'way':
	print_object("ways", xmltag['id'], "", "", xmltag['visible'], xmltag['user'], xmltag['timestamp'])
	seq = 1
  elif xmltag.name == 'relation':
	print_object("relations", xmltag['id'], "", "", xmltag['visible'], xmltag['user'], xmltag['timestamp'])

  # Print tags
  elif (xmltag.name == 'tag') and (xmltag.parent.name == 'node'):
	print_tag("node_tags", xmltag.parent['id'], xmltag['k'], xmltag['v'])
  elif (xmltag.name == 'tag') and (xmltag.parent.name == 'way'):
    print_tag("way_tags", xmltag.parent['id'], xmltag['k'], xmltag['v'])
  elif (xmltag.name == 'tag') and (xmltag.parent.name == 'nd') and (xmltag.parent.parent.name == 'way'):
	print_tag("way_tags", xmltag.parent.parent['id'], xmltag['k'], xmltag['v'])
  elif (xmltag.name == 'tag') and (xmltag.parent.name == 'relation'):
	print_tag("relation_tags", xmltag.parent['id'], xmltag['k'], xmltag['v'])
  elif (xmltag.name == 'tag') and (xmltag.parent.name == 'member') and (xmltag.parent.parent.name == 'relation'):
	print_tag("relation_tags", xmltag.parent.parent['id'], xmltag['k'], xmltag['v'])
  
  # Print way nodes
  elif (xmltag.name == 'nd') and (xmltag.parent.name == 'way'): 
	print_way_nodes(xmltag.parent['id'], xmltag['ref'], seq)
	seq+=1  
  
  # Print relation members
  elif (xmltag.name == 'member') and (xmltag.parent.name == 'relation'): 
    print_member(xmltag['type'], xmltag.parent['id'], xmltag['ref'], xmltag['role'])
	
  #else:
	#ofile.write(xmltag.name + "_" + xmltag.parent.name + "\n")

# Example
#osm2sql.py example.osm > example.sql